Grid Master
Business Opportunity
The objective of this project is to automate the process of converting a complex Excel grid containing business rules into a digital format and loading it into a database. The digitized rules must be efficiently displayed on a user interface (UI) for end users to access. The Excel sheets consist of intricate tables created within cells, formed by merging rows and cells. The challenge lies in accurately extracting and transferring the data at each row level from the Excel sheets to the database, while also properly handling the merged rows and cells as a coherent entity
Link to Canvas Dashboard :Grid Master
Solution / Approach
The process of reconstructing a table with rows, columns, merged cells, and nested tables in the UI involves several steps. First, the specific range of cells in the Excel grid is identified to focus on extracting data from the relevant cells only. Data, headers, and formatting are then extracted from each cell within the identified range, with merged cells treated as a single unit. If any nested tables are found within the grid, the same approach is recursively applied to handle them effectively. By systematically processing each cell, a hierarchical JSON structure is constructed, preserving the relationships between rows and columns. This JSON representation is stored in MongoDB, a flexible and scalable database system. APIs are then exposed to allow users to consume the digitized data, providing access to the transformed Excel grid for retrieval, manipulation, and analysis. To recreate the original table structure, the JSON structure is iterated recursively, extracting the necessary information. This process ensures that the data and structure of the Excel grid are accurately captured and allows for convenient interaction with the data in the UI.
By combining the steps of digitizing the Excel grid, storing the data in MongoDB, and providing APIs for data consumption, we create a comprehensive solution. This solution not only transforms the data from Excel to JSON but also makes it easily accessible and usable for further analysis and processing.
Architecture
Key Metrics
- Conversion Accuracy: The accuracy of the converted digital format is crucial to ensure that all data, headers, formatting, merged cells, and nested tables are accurately captured from the Excel grid.
- Processing Time: The efficiency of the solution in terms of processing time is important to handle large and complex Excel grids within a reasonable time frame. The solution should be able to process the data efficiently without significant performance degradation.
- Data Integrity: The solution should ensure the integrity of the converted data, preventing any loss or corruption during the conversion process. The converted data should closely match the original Excel grid without any discrepancies.
- Database Integration: The integration with MongoDB as the database system should be seamless and reliable. The solution should effectively store the digitized data in MongoDB, ensuring data consistency and accessibility.
- API Performance: The performance of the exposed APIs for data consumption is crucial for users to retrieve, manipulate, and analyze the converted data conveniently. The APIs should provide fast response times and handle multiple concurrent requests effectively.
Tech Stack
- React JS
- Mongo DB
- Flask
Resources Links
Feedback
We appreciate your feedback! Please provide us with any suggestions or improvements you have for our product.Please provide feedback on this product by clicking the following Link: